﻿Imports System.Data.SqlServerCe

Namespace Data
    Public Class Setup
        Public Shared Function IsUpToDate() As Boolean
            If Not IsLocalDatabaseSetup Then Return False
            If Not IsLocalDatabaseUpToDate Then Return False
            Return True
        End Function

        Public Shared ReadOnly Property IsLocalDatabaseSetup() As Boolean
            Get
                If String.IsNullOrEmpty(Environment.DBPath) Then Return False
                If Not System.IO.File.Exists(Environment.DBPath) Then Return False
                If String.IsNullOrEmpty(Environment.DBPassword) Then Return False
                Return True
            End Get
        End Property
        Public Shared ReadOnly Property IsLocalDatabaseUpToDate() As Boolean
            Get
                Return MetaData.GetDBVersion(Source.Local) = MetaData.GetDBVersion(Source.Template)
            End Get
        End Property

        Public Shared Function DoesLocalDatabaseNeedUpgrade() As Boolean
            Dim iLocalVersion As Integer = MetaData.GetDBVersion(Source.Local)
            Dim iTemplateVersion As Integer = MetaData.GetDBVersion(Source.Template)
            Return iLocalVersion <> iTemplateVersion
        End Function

        Public Shared Function CanUpgradeFromCurrentVersion(ByRef whyNot As String) As Boolean
            Dim iLocalVersion As Integer = MetaData.GetDBVersion(Source.Local)
            Dim iTemplateVersion As Integer = MetaData.GetDBVersion(Source.Template)
            For i As Integer = iLocalVersion To iTemplateVersion - 1
                Dim aStatements As Generic.List(Of String)
                aStatements = GetUpgradeStatements(i, i + 1)
                If aStatements.Count = 0 Then
                    whyNot = String.Format("Your local database is of version {0} and needs to be upgraded to version {1}. However, upgrade statements from version {2} to {3} are missing.", iLocalVersion, iTemplateVersion, i, i + 1)
                    Return False
                End If
            Next
            Return True
        End Function

        Public Shared Sub UpgradeLocalDatabase(ByVal justCreated As Boolean)
            If justCreated Then
                CopyTablesFromTemplateToLocal()
            Else
                Dim iLocalVersion As Integer = MetaData.GetDBVersion(Source.Local)
                Dim iTemplateVersion As Integer = MetaData.GetDBVersion(Source.Template)
                If iLocalVersion <> iTemplateVersion Then
                    CreateBackup(iLocalVersion)
                    RunUpgradeStatements(iLocalVersion, iTemplateVersion)
                End If
            End If
        End Sub

        Private Shared Sub RunUpgradeStatements(ByVal currentLocalVersion As Integer, ByVal templateVersion As Integer)
            Using t As New TransactionScope
                For i As Integer = currentLocalVersion To templateVersion - 1
                    Dim aStatements As Generic.List(Of String)
                    aStatements = GetUpgradeStatements(i, i + 1)

                    For Each strStatement As String In aStatements
                        Environment.ExecuteNonQuery(strStatement, t)
                    Next
                Next
                'now, set the version in the metadata table
                MetaData.SetDBVersion(templateVersion, t)
                t.Complete()
            End Using
        End Sub

        Private Shared Function GetUpgradeStatements(ByVal fromVersion As Integer, ByVal toVersion As Integer) As Generic.List(Of String)
            Dim strSql As String = "SELECT SqlStatement FROM DBUpgradeStatements WHERE FromVersion=@FromVersion AND ToVersion=@ToVersion ORDER BY ExecutionOrder"
            Dim sqlCmd As Command = Environment.NewCommand(strSql, Source.Template)
            sqlCmd.AddParameterWithValue("@FromVersion", fromVersion)
            sqlCmd.AddParameterWithValue("@ToVersion", toVersion)
            Return Environment.ExecuteCommandToStringList(sqlCmd)
        End Function

        Public Shared Sub UpgradeDbFileFormat(ByVal path As String, ByVal password As String)
            'Tax
            'Dim strConn As String = Environment.GetConnectionString(path, password, True)
            Dim strConn As String = Environment.GetConnectionString(path, password)
            Using clsEngine As New SqlCeEngine(strConn)
                clsEngine.Upgrade()
            End Using
        End Sub

        Private Shared Sub CopyTablesFromTemplateToLocal()
            Dim aNeededTables As Generic.List(Of String)
            aNeededTables = Environment.SqlToStringList("select table_name from information_schema.tables", Source.Template)

            Dim iTemplateVersion As Integer = MetaData.GetDBVersion(Source.Template)

            Using t As New TransactionScope(Source.Local)

                For Each strTableName As String In aNeededTables

                    Dim strCreateTableSql As String = GetCreateTableStatement(strTableName)
                    Environment.ExecuteNonQuery(strCreateTableSql, t)

                    Dim aIndexes() As Environment.IndexInfo
                    aIndexes = Environment.GetIndexInfos_Template(strTableName)
                    For Each clsIdx As Environment.IndexInfo In aIndexes
                        If Not clsIdx.IsPrimaryKey Then
                            Dim clsSql As New System.Text.StringBuilder("CREATE")
                            If clsIdx.IsUnique Then clsSql.Append(" UNIQUE")
                            clsSql.Append(" INDEX ")
                            clsSql.Append(clsIdx.Name)
                            clsSql.Append(" ON ")
                            clsSql.Append(strTableName)
                            clsSql.Append(" ([")
                            Dim blnFirst As Boolean = True
                            For Each strColName As String In clsIdx.ColNames
                                If blnFirst Then
                                    blnFirst = False
                                Else
                                    clsSql.Append(",[")
                                End If
                                clsSql.Append(strColName)
                                clsSql.Append("]"c)
                            Next
                            clsSql.Append(")"c)

                            Environment.ExecuteNonQuery(clsSql.ToString, t)
                        End If
                    Next
                Next
                MetaData.SetDBVersion(iTemplateVersion, t)
                t.Complete()
            End Using
        End Sub

        Private Shared Sub SetCorrectVersion(ByVal transactionScope As TransactionScope)
            Dim iVersion As Integer = MetaData.GetDBVersion(Source.Template)
            MetaData.SetDBVersion(iVersion, transactionScope)
        End Sub

        Public Shared Sub CreateBackup()
            Dim iVersion As Integer = MetaData.GetDBVersion(Source.Local)
            CreateBackup(iVersion)
        End Sub

        Private Shared Sub CreateBackup(ByVal currentLocalVersion As Integer)
            'Dim iVersion As Integer = MetaData.GetDBVersion(Source.Local)
            Dim strFileName As String = System.IO.Path.GetFileNameWithoutExtension(Environment.DBPath)
            Dim strFolder As String = System.IO.Path.GetDirectoryName(Environment.DBPath)
            Dim strBackupFileName As String = System.IO.Path.Combine(strFolder, String.Format("{0}_v{1}.sdf", strFileName, currentLocalVersion))
            If System.IO.File.Exists(strBackupFileName) Then System.IO.File.Delete(strBackupFileName)

            System.IO.File.Copy(Environment.DBPath, strBackupFileName)
        End Sub
        Private Shared Function GetColDef(ByVal col As Environment.ColumnInfo) As String
            Dim clsSb As New System.Text.StringBuilder("COLUMN ")
            clsSb.Append(col.Name)
            clsSb.Append(" ")
            clsSb.Append(col.DataType)
            clsSb.Append(" ")
            If col.CharMaxLen > 0 AndAlso col.DataType.ToLower <> "ntext" Then
                clsSb.Append("("c)
                clsSb.Append(col.CharMaxLen)
                clsSb.Append(")"c)
            End If
            If col.IsNullable Then
                clsSb.Append(" NULL")
            Else
                clsSb.Append(" NOT NULL")
            End If
            If col.DefaultValue IsNot Nothing Then
                clsSb.Append(" DEFAULT ")
                clsSb.Append(col.DefaultValue)
            End If
            Return clsSb.ToString
        End Function

        Private Shared Function GetCreateTableStatement(ByVal tableName As String) As String
            Dim clsSb As New System.Text.StringBuilder

            Dim aCols As Generic.List(Of Environment.ColumnInfo)
            aCols = Environment.GetColumnInfos_Template(tableName)

            Dim aIndexes() As Environment.IndexInfo
            aIndexes = Environment.GetIndexInfos_Template(tableName)

            clsSb.Append("CREATE TABLE ")
            clsSb.Append(tableName)
            clsSb.Append(" (")

            Dim blnAddComma As Boolean = False
            For Each clsCol As Environment.ColumnInfo In aCols
                If blnAddComma Then
                    clsSb.Append(", ")
                Else
                    blnAddComma = True
                End If

                clsSb.Append("["c)
                clsSb.Append(clsCol.Name)
                clsSb.Append("] ")
                clsSb.Append(clsCol.DataType)
                If clsCol.CharMaxLen > 0 Then
                    Select Case clsCol.DataType.ToLower
                        Case "ntext", "image"
                            'don't write size
                        Case Else
                            clsSb.Append(" (")
                            clsSb.Append(clsCol.CharMaxLen)
                            clsSb.Append(")"c)
                    End Select
                End If
                If Not clsCol.IsNullable Then clsSb.Append(" NOT NULL")
                If clsCol.AutoInc_Increment > 0 Then
                    clsSb.Append(" IDENTITY(")
                    clsSb.Append(clsCol.AutoInc_Increment)
                    clsSb.Append(","c)
                    clsSb.Append(clsCol.AutoInc_Seed)
                    clsSb.Append(")"c)
                End If
            Next

            For Each clsIdx As Environment.IndexInfo In aIndexes
                If clsIdx.IsPrimaryKey Then
                    If blnAddComma Then
                        clsSb.Append(", ")
                    Else
                        blnAddComma = True
                    End If
                    clsSb.Append(" PRIMARY KEY (")
                    Dim blnFirst As Boolean = True
                    For Each strColName As String In clsIdx.ColNames
                        If Not blnFirst Then
                            clsSb.Append(","c)
                        Else
                            blnFirst = False
                        End If
                        clsSb.Append("["c)
                        clsSb.Append(strColName)
                        clsSb.Append("]"c)
                    Next
                    clsSb.Append(")"c)
                End If
            Next

            clsSb.Append(")"c)
            Return clsSb.ToString
        End Function

    End Class

End Namespace